
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System;
using IPAMS.Common;
using IPAMS.Data;
using IPAMS.Entities;
using IPAMS.Views;

namespace IPAMS.Views
{
    /// <summary>
    /// This is the main Form of the project.
    /// The evaluation proccess is done in here.
    /// </summary>
    public partial class IPAMSForm : Form
    {
        #region Fields
        
        private int currentStep;

        /// <summary>
        /// The main data set used to store all the tables.
        /// </summary>
        public DSIPAMS dsipams;

        /// <summary>
        /// An updated list of cash used in the portfolio. 
        /// </summary>
        public List<Cash> portfolioCash;

        /// <summary>
        /// An updated list of the stocks of the portfolio.
        /// </summary>
        public List<Stock> portfolioStocks;


        /// <summary>
        /// The current investment house handling the portfolio.
        /// </summary>
        private string currentInvestmentHouse = "None Selected";


        /// <summary>
        /// The date when we start evaluating the portfolio (not necessarily the date of the first action)
        /// </summary>
        private DateTime startDateTime = DateTime.Now;


        /// <summary>
        /// The date when we finish evaluating the portfolio (not necessarily the date of the last action) 
        /// </summary>
        private DateTime endDateTime = DateTime.Now;



        /// <summary>
        /// The folder containing the data files. 
        /// </summary>
        private string dataFilesLocation = Application.StartupPath + @"\DummyData";


        #endregion

        #region UI Controls (editors)

        //These are the UI controls used in by the main form.
        //The main form is responsible for connecting and handling the actions done by the controls. 
        //The controls are responsible of providing events, methods and properties for the main 
        //form to handle them correctly. 
        //Not all functionality is implemented in the controls. 
        private PortfolioSetup portfolioSetup;
        private StocksEditor stocksEditor;
        private TablesEditor tablesEditor;
        private CurrenciesEditor currenciesEditor;
        private QuotesEditor quotesEditor;
        private CurrencyExchangeRatesEditor currencyExchangeRatesEditor;
        private DividendsEditor dividendsEditor;
        private QuantityTransactionEditor transactionEditor;
        private CashSetup cashSetup;
        private EvaluationEditor evaluationEditor;
        private InvestmentHousesEditor investmentHousesEditor;
        private CashEditor cashEditor;
        private TaxesEditor taxesEditor;
        private ChargesEditor chargesEditor;
        private CountriesEditor countriesEditor;
        private StockExchangesEditor stockExchangesEditor;


        #endregion

        #region Events Handlers


        public EventHandler<TEventArgs<string>> OnStocksFileSelected;
        public EventHandler<TEventArgs<bool>> OnUpdateCurrencies;
        public EventHandler<TEventArgs<DateTime>> OnSetStartDate;
        public EventHandler<TEventArgs<DateTime>> OnSetEndDate;
        public EventHandler<TEventArgs<string>> OnSetInvestmentHouse;

        #endregion


        /// <summary>
        /// Initializes a new instance of the <see cref="IPAMSForm"/> class.
        /// </summary>
        public IPAMSForm()
        {
            InitializeComponent();
            
            InitializeComponentExtended();

            InitializeEventsHandlers();
            //loading initial data from dummy files
            #region Currencies


            dsipams.DTCurrencies.Clear();
            /*dsipams.DTCurrencies.Merge(
                currenciesEditor.LoadData(Application.StartupPath + @"\DummyData\Currencies.csv"));*/
            dsipams.DTCurrencies.AcceptChanges();

            
            #endregion
            #region Stocks

            dsipams.Tables["DTStocks"].Clear();
            dsipams.Tables["DTStocks"].Merge(stocksEditor.LoadData(Application.StartupPath + @"\DummyData\Stocks.csv"));
            dsipams.Tables["DTStocks"].AcceptChanges();

            #endregion
            #region Stock Exchange Rates (Quotes)
            
            dsipams.Tables["DTQuotes"].Clear();
            dsipams.Tables["DTQuotes"].Merge(quotesEditor.LoadData(Application.StartupPath + @"\DummyData\Quotes.csv"));
            dsipams.Tables["DTQuotes"].AcceptChanges();

            #endregion
            #region Currencies Exchange Rates

            dsipams.Tables["DTCurrencyExchangeRate"].Clear();
            dsipams.Tables["DTCurrencyExchangeRate"].Merge(currencyExchangeRatesEditor.LoadData(Application.StartupPath + @"\DummyData\CurrenciesExchangeRates.csv"));
            dsipams.Tables["DTCurrencyExchangeRate"].AcceptChanges();
            
            #endregion
            #region Dividends
         
            dsipams.Tables["DTDividends"].Clear();
            dsipams.Tables["DTDividends"].Merge(dividendsEditor.LoadData(Application.StartupPath + @"\DummyData\Dividends.csv"));
            dsipams.Tables["DTDividends"].AcceptChanges();
            
            #endregion
            #region Investment Management Houses

            dsipams.Tables["DTInvestmentManagementHouses"].Clear();
            dsipams.Tables["DTInvestmentManagementHouses"].Merge(
                investmentHousesEditor.LoadData(Application.StartupPath + @"\DummyData\InvestmentHouses.csv"));
            dsipams.Tables["DTInvestmentManagementHouses"].AcceptChanges();

            #endregion
            #region Quantity Transactions

            dsipams.Tables["DTQuantityTransactions"].Clear();
            dsipams.Tables["DTQuantityTransactions"].Merge(
                transactionEditor.LoadData(Application.StartupPath + @"\DummyData\QuantityTransactions.csv"));
            dsipams.Tables["DTQuantityTransactions"].AcceptChanges();

            #endregion
            #region Cash
 
            dsipams.Tables["DTCash"].Clear();
            dsipams.Tables["DTCash"].Merge(cashEditor.LoadData(Application.StartupPath + @"\DummyData\Cash.csv"));
            dsipams.Tables["DTCash"].AcceptChanges();

            #endregion
            #region Taxes

            dsipams.Tables["DTTaxes"].Clear();
            dsipams.Tables["DTTaxes"].Merge(taxesEditor.LoadData(Application.StartupPath + @"\DummyData\Taxes.csv"));
            dsipams.Tables["DTTaxes"].AcceptChanges();

            #endregion
            #region Charges

            dsipams.Tables["DTCharges"].Clear();
            dsipams.Tables["DTCharges"].Merge(chargesEditor.LoadData(Application.StartupPath + @"\DummyData\Charges.csv"));
            dsipams.Tables["DTCharges"].AcceptChanges();

             #endregion
            #region Countries

            dsipams.Tables["DTCountries"].Clear();
            dsipams.Tables["DTCountries"].Merge(countriesEditor.LoadData(Application.StartupPath + @"\DummyData\Countries.csv"));
            dsipams.Tables["DTCountries"].AcceptChanges();

             #endregion
            #region StockExchanges

            dsipams.Tables["DTStockExchanges"].Clear();
            dsipams.Tables["DTStockExchanges"].Merge(stockExchangesEditor.LoadData(Application.StartupPath + @"\DummyData\StockExchanges.csv"));
            dsipams.Tables["DTStockExchanges"].AcceptChanges();

            #endregion

        
            List<string> availableStocks = new List<string>();
            foreach(DSIPAMS.DTStocksRow stock in dsipams.Tables["DTStocks"].Rows)
            {
                availableStocks.Add(stock["STOCK_SYMBOL"].ToString());
            }
            quotesEditor.SetStocksFilterSelection(availableStocks);
           // UpdateInformation();
        }

        /// <summary>
        /// Initializes more components.
        /// </summary>
        private void InitializeComponentExtended()
        {
            dsipams = new DSIPAMS();
            currentStep = 0;
          
            //setting up the tables editor.

            #region Tables View

            portfolioSetup = new PortfolioSetup();
            portfolioSetup.Dock = DockStyle.Fill;
            portfolioSetup.Initialize();


            tablesEditor = new TablesEditor();
            tablesEditor.Dock = DockStyle.Fill;

            stocksEditor = new StocksEditor();
            stocksEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageStocks.Controls.Add(stocksEditor);

            currenciesEditor = new CurrenciesEditor(new IPAMSDataSet());
            currenciesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageCurrencies.Controls.Add(currenciesEditor);

            quotesEditor = new QuotesEditor();
            quotesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageQuotes.Controls.Add(quotesEditor);

            currencyExchangeRatesEditor = new CurrencyExchangeRatesEditor();
            currencyExchangeRatesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageCurrenciesExchangeRates.Controls.Add(currencyExchangeRatesEditor);

            dividendsEditor = new DividendsEditor();
            dividendsEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageDividends.Controls.Add(dividendsEditor);

            investmentHousesEditor = new InvestmentHousesEditor();
            investmentHousesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageInvestmentHouses.Controls.Add(investmentHousesEditor);

            cashEditor = new CashEditor();
            cashEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageCash.Controls.Add(cashEditor);

            taxesEditor = new TaxesEditor();
            taxesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageTaxes.Controls.Add(taxesEditor);

            chargesEditor = new ChargesEditor();
            chargesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageCharges.Controls.Add(chargesEditor);

            countriesEditor = new CountriesEditor();
            countriesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageCountries.Controls.Add(countriesEditor);

            stockExchangesEditor = new StockExchangesEditor();
            stockExchangesEditor.Dock = DockStyle.Fill;
            tablesEditor.tabPageStockExchanges.Controls.Add(stockExchangesEditor);

            #endregion


            transactionEditor = new QuantityTransactionEditor();
            transactionEditor.Dock = DockStyle.Fill;

            cashSetup = new CashSetup();
            cashSetup.Dock = DockStyle.Fill;
            cashSetup.dgvCash.DataSource = cashSetup.LoadData(Application.StartupPath + @"\DummyData\Cash.csv");
            buttonBack.Enabled = false;

            evaluationEditor = new EvaluationEditor();
            evaluationEditor.Dock = DockStyle.Fill;


            //adding all the controls to the main form 

            groupBoxCurrentView.Controls.Add(portfolioSetup);
            groupBoxCurrentView.Controls.Add(cashSetup);
            groupBoxCurrentView.Controls.Add(tablesEditor);
            groupBoxCurrentView.Controls.Add(transactionEditor);
            groupBoxCurrentView.Controls.Add(evaluationEditor);
            groupBoxCurrentView.Controls[1].Hide();
            groupBoxCurrentView.Controls[2].Hide();
            groupBoxCurrentView.Controls[3].Hide();
            groupBoxCurrentView.Controls[4].Hide();

            groupBoxCurrentView.Controls[0].Dock = DockStyle.Fill;

        }

        /// <summary>
        /// Initializes the events handlers.
        /// </summary>
        private void InitializeEventsHandlers()
        {
            OnSetStartDate = SetStartDate;
            OnSetEndDate = SetEndDate;
            OnSetInvestmentHouse = SetInvestmentHouse;


            OnStocksFileSelected = UpdateStocksGridView;
            stocksEditor.StocksFileSelected += OnStocksFileSelected;

            portfolioSetup.StartDateChanged += OnSetStartDate;
            portfolioSetup.EndDateChanged += OnSetEndDate;

            portfolioSetup.InvestmentHouseChanged += OnSetInvestmentHouse;
            

        }


        /// <summary>
        /// Updates the stocks grid view.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="stocksFilePath">The <see cref="string"/> instance containing the event data.</param>
        public void UpdateStocksGridView(object sender, TEventArgs<string> stocksFilePath)
        {
            DSIPAMS.DTStocksDataTable stocks = new DSIPAMS.DTStocksDataTable();

            string stocksFile = stocksFilePath.data;
            StreamReader sr = new StreamReader(stocksFile);
            string[] stocksHeaders = sr.ReadLine().Split(',');
            while (true)
            {
                string stock = sr.ReadLine();
                if (stock == string.Empty || stock == null) break;
                string[] stockInfo = stock.Split(',');
                if (stockInfo.Length != 4) continue;
                stocks.AddDTStocksRow(stockInfo[0], stockInfo[1], stockInfo[2], stockInfo[3]);
            }
            sr.Close();

            dsipams.Tables["DTStocks"].Merge(stocks);
   
            stocksEditor.Text = stocksFile;
            stocksEditor.dgvStocks.DataSource = dsipams.Tables["DTStocks"];
            
            for (int colIndex = 0; colIndex < stocksHeaders.Length; colIndex++ )
            {
                stocksEditor.dgvStocks.Columns[colIndex].HeaderText = stocksHeaders[colIndex];
            }

            stocksEditor.dgvStocks.Refresh();
        }

        #region UI Behaviour 

        /// <summary>
        /// Handles the Click event of the buttonSaveData control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        private void buttonSaveData_Click(object sender, EventArgs e)
        {
            transactionEditor.SaveData(Application.StartupPath + @"\DummyData\QuantityTransactions.csv");
            currencyExchangeRatesEditor.SaveData(Application.StartupPath + @"\DummyData\CurrenciesExchangeRates.csv");
            currenciesEditor.SaveData(Application.StartupPath + @"\DummyData\Currencies.csv");
            quotesEditor.SaveData(Application.StartupPath + @"\DummyData\Quotes.csv");
            cashEditor.SaveData(Application.StartupPath + @"\DummyData\Cash.csv");
        }


        /// <summary>
        /// Handles the Click event of the buttonNext control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        private void buttonNext_Click(object sender, EventArgs e)
        {
            switch (currentStep)
            {
                case 0:
                    if (currentInvestmentHouse == "None Selected" || currentInvestmentHouse == string.Empty)
                    {
                        MessageBox.Show(
                            "The portfolio cannot be evaluated without choosing an Investment House.",
                            "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        return;
                    }
                    if (startDateTime > endDateTime)
                    {
                        MessageBox.Show(
                            "The portfolio cannot be evaluated without choosing a valid Start and End time..",
                            "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        return;
                    }
                    groupBoxCurrentView.Controls[0].Hide();
                    groupBoxCurrentView.Controls[1].Show();
                    
                    currentStep++;
                    buttonBack.Enabled = true;
                    break;

                case 1:
                    groupBoxCurrentView.Controls[1].Hide();
                    groupBoxCurrentView.Controls[2].Show();
                    dsipams.Tables["DTCash"].Clear();
                    dsipams.Tables["DTCash"].Merge(cashSetup.cash);
                    currentStep++;
                    break;

                case 2:
                    buttonBack.Enabled = true;
                    groupBoxCurrentView.Controls[2].Hide();
                    groupBoxCurrentView.Controls[3].Show();
                    currentStep++;
                    buttonNext.Text = "Evaluate>";
                    panelEval.Visible = true;
                    break;

                case 3:
                    buttonNext.Enabled = false;
                    groupBoxCurrentView.Controls[3].Hide();
                    groupBoxCurrentView.Controls[4].Show();
                    currentStep++;
                    panelEval.Visible = false;
                    PerformEvaluation();
                    break;


            }
        }

        /// <summary>
        /// Handles the Click event of the buttonBack control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        private void buttonBack_Click(object sender, EventArgs e)
        {
            switch (currentStep)
            {
                case 4:
                    buttonNext.Enabled = true;
                    groupBoxCurrentView.Controls[4].Hide();
                    groupBoxCurrentView.Controls[3].Show();
                    currentStep--;
                    panelEval.Visible = true;
                    buttonNext.Text = "Evaluate>";
                    break;

                case 3:
                    groupBoxCurrentView.Controls[3].Hide();
                    groupBoxCurrentView.Controls[2].Show();
                    currentStep--;
                    panelEval.Visible = false;
                    buttonNext.Text = "Next>";
                    break;

                case 2:
                   
                    groupBoxCurrentView.Controls[2].Hide();
                    groupBoxCurrentView.Controls[1].Show();
                    currentStep--;
                    break;

                case 1:
                    buttonBack.Enabled = false;
                    groupBoxCurrentView.Controls[1].Hide();
                    groupBoxCurrentView.Controls[0].Show();
                    currentStep--;

                    break;
            }
        }

        #endregion


        /// <summary>
        /// Performs the evaluation. 
        /// The results are in the tables currentCash, currentStocks and transactions. 
        /// </summary>
        private void PerformEvaluation()
        {
            List<IAction> evaluationActions = new List<IAction>();

            #region Build the actions list (Sell/Buy commissions and Taxes)

            //create the sequential actions after each buy/sell
            //i.e. create the Actions List
            if (transactionEditor.transactions != null && transactionEditor.transactions.Rows.Count > 0)
            {
                foreach (DSIPAMS.DTQuantityTransactionsRow row in transactionEditor.transactions.Rows)
                {
                    IAction currentAction;
                    string stockSymbol = row.QUANTITY_TRANSACTION_STOCK_SYMBOL;
                    DateTime transactionDate = row.QUANTITY_TRANSACTION_DATE;
                    double stockPrice = EvaluateStockPriceOnDate(transactionDate, stockSymbol);
                    Currency stockCurrency = GetStockCurrency(stockSymbol);
                    Currency transactionCurrency = GetTransactionCurrency(row);


                    if (string.Format(row.QUANTITY_TRANSACTION_TYPE).ToUpper() == "BUY")
                    {
                        currentAction = new BuyAction(transactionDate,
                                                      stockPrice,
                                                      new Stock(GetStockNameBySymbol(stockSymbol),
                                                                stockSymbol,
                                                                stockCurrency),
                                                      row.QUANTITY_TRANSACTION_STOCK_QUANTITY,
                                                      transactionCurrency);


                        //find and add the buy/taxes commissions.
                        List<IAction> buyCommissions = GetAfterBuyCommissions((BuyAction)currentAction);
                        List<IAction> buyTaxes = GetAfterBuyTaxes((BuyAction)currentAction);
                        ((BuyAction)currentAction).AfterBuyAction.AddRange(buyCommissions);
                        ((BuyAction)currentAction).AfterBuyAction.AddRange(buyTaxes);
                        //add the actions to the execution list                 
                        evaluationActions.Add(currentAction);
                        evaluationActions.AddRange(buyCommissions);
                        evaluationActions.AddRange(buyTaxes);
                    }

                    else if (string.Format(row.QUANTITY_TRANSACTION_TYPE).ToUpper() == "SELL")
                    {
                        currentAction = new SellAction(transactionDate,stockPrice,new Stock(GetStockNameBySymbol(stockSymbol),
                                                                                            stockSymbol,stockCurrency),row.QUANTITY_TRANSACTION_STOCK_QUANTITY,transactionCurrency);

                        //find and add the sell/taxes commissions.
                        List<IAction> sellCommissions = GetAfterSellCommissions((SellAction)currentAction);
                        List<IAction> sellTaxes = GetAfterSellTaxes((SellAction)currentAction);

                        ((SellAction)currentAction).AfterSellAction.AddRange(sellCommissions);
                        ((SellAction)currentAction).AfterSellAction.AddRange(sellTaxes);
                        //add the actions to the execution list          
                        evaluationActions.Add(currentAction);
                        evaluationActions.AddRange(sellCommissions);
                        evaluationActions.AddRange(sellTaxes);
                    }
                }
            }

            #endregion

            #region Add periodical fees and taxes

            //here we add the periodical actions.
            //for that we need the portfolio start date and end date. 
            //the dates will also be used to apply the actions done ONLY between these dates. 
            //the list retrieved by the GetPeriodicalCommissions is sorted by dates.
            List<IAction> periodicalFeesList = GetPeriodicalCommissions();


           // IAction nextAction = GetNextValidActionForPeriodical(evaluationActions,null);
            List<IAction> correctedList = new List<IAction>();




            while (periodicalFeesList.Count > 0 || evaluationActions.Count > 0)
            {
                //end of list...
                //no actions left in the original list, add all the fees until the end of the period and exit...
                if (evaluationActions.Count == 0)
                {
                    correctedList.AddRange(periodicalFeesList);
                    periodicalFeesList.Clear();
                }
                else //there are evaluation actions in the list
                {
                    if (evaluationActions[0].ActionType == ActionTypes.Buy ||
                        evaluationActions[0].ActionType == ActionTypes.Sell)
                    {
                        //add all the action that come BEFORE the evaluation action
                        while (periodicalFeesList.Count > 0 &&
                               periodicalFeesList[0].ActionTime <= evaluationActions[0].ActionTime)
                        {
                            correctedList.Add(periodicalFeesList[0]);
                            periodicalFeesList.Remove(periodicalFeesList[0]);
                        }
                        //add the action itself to the list
                        correctedList.Add(evaluationActions[0]);
                        evaluationActions.Remove(evaluationActions[0]);
                    }
                    else //action are of the same time of the last Buy/Sell action...
                    {
                        correctedList.Add(evaluationActions[0]);
                        evaluationActions.Remove(evaluationActions[0]);
                    }
                }
            }

            if(evaluationActions.Count !=0 || periodicalFeesList.Count != 0)
            {
                MessageBox.Show("Error evaluating portfolio.\nAn error occured while adding the periodical fees.",
                                        "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;

            }
            #endregion

            evaluationActions.AddRange(correctedList);


            DSIPAMS.DTFullTransactionsDataTable transactions = new DSIPAMS.DTFullTransactionsDataTable();
            DSIPAMS.DTCurrentCashDataTable currentCash = new DSIPAMS.DTCurrentCashDataTable();

            foreach (DSIPAMS.DTCashRow cashRow in dsipams.Tables["DTCash"].Rows)
            {
                currentCash.AddDTCurrentCashRow(cashRow.CASH_CURRENCY, cashRow.CASH_AMOUNT);
            }

            // currentCash.Merge(dsipams.Tables[Common.TablesNames.DTCash]);

            DSIPAMS.DTCurrentStocksDataTable currentStocks = new DSIPAMS.DTCurrentStocksDataTable();

            #region Getting the portfolio base currency

            string portfolioBaseCurrencySymbol =  GetPortfolioBaseCurrencySymbol();

            #endregion
            //Execute the actions in the list one by one by order of appearance.
            DSIPAMS.DTCurrentCashRow[] currenctCashRows;

            #region Perform the actions

            foreach (IAction action in evaluationActions)
            {
                if (action.ActionTime < startDateTime || action.ActionTime > endDateTime) continue;
                string empt = string.Empty;
                double exchangeRate = 0;
                double value = 0;
                switch(action.ActionType)
                {
                        #region Perform Buy

                    case ActionTypes.Buy:
          
                        //add the stocks to the stocks table or update the stock row if already in table
                        //reduce the cash from the cash table.
                        //if there is a cash of the correct type - reduce from it, other - reduce the cash from 
                        //another type according to exchange rate.

                        //when buying the stock, we have the correct cash to buy it with.
                        //the action currency is set by the user. the currency can be the same of the stock. or different.
                        //if the currency exists in the portfolio, we can reduce from it. otherwise, 
                        //we need to exchange it. If the cash is different from the stock, we need to translate the price for the action with
                        //the exchange rate tables. 

                        currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                           string.Format("CASH_CURRENCY = '{0}'", ((BuyAction)action).ActionCurrency.Symbol));

                        //no need to exchange the stock price to the action's currency
                        if (((BuyAction)action).ActionCurrency.Symbol == ((BuyAction)action).StockInfo.StockCurrency.Symbol)
                        {
                            //the type of cash the action is taken in exists in the cash table.
                            if (currenctCashRows.Length == 1)
                            {
                                value = ((BuyAction) action).BuyPrice*((BuyAction) action).Quantity;
                                currenctCashRows[0].CASH_AMOUNT -= ((BuyAction)action).BuyPrice * ((BuyAction)action).Quantity;
                            }
                            else //this type of cash does not exist, the portfolio cash MUST exist in the folder or we have an exception
                            {
                                exchangeRate =
                                    GetCurrencyExchangeRate(((BuyAction)action).ActionCurrency.Symbol,
                                                            portfolioBaseCurrencySymbol, ((BuyAction)action). ActionTime);

                                currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                                   string.Format("CASH_CURRENCY = '{0}'", portfolioBaseCurrencySymbol));

                                if (currenctCashRows.Length != 1)
                                {
                                    MessageBox.Show(
                                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                    return;
                                }
                                else
                                {
                                    value = ((BuyAction)action).BuyPrice * ((BuyAction)action).Quantity * exchangeRate;
                                    currenctCashRows[0].CASH_AMOUNT -= ((BuyAction)action).BuyPrice * ((BuyAction)action).Quantity * exchangeRate;
                                   
                                }
                            }
                        }
                        else //the action is taken in a different currency then the stock, need to translate the stock price to the action currency
                        {
                            double stockPriceToActionRate =
                                GetCurrencyExchangeRate(((BuyAction) action).ActionCurrency.Symbol,
                                                        ((BuyAction) action).StockInfo.StockCurrency.Symbol,
                                                        ((BuyAction) action). ActionTime);


                            //the type of cash the action is taken in exists in the cash table.
                            if (currenctCashRows.Length == 1)
                            {
                                //the price now is the price in the stocks currency, we need to change it with the rate we found
                                currenctCashRows[0].CASH_AMOUNT -= ((BuyAction) action).BuyPrice*
                                                                   ((BuyAction) action).Quantity*stockPriceToActionRate;
                                value = ((BuyAction)action).BuyPrice *
                                        ((BuyAction)action).Quantity * stockPriceToActionRate;
                            }
                            else//this type of cash does not exist, the portfolio cash MUST exist in the folder or we have an exception
                            {
                                //we need to exchange also the action to the cash we have 

                                exchangeRate =
                                    GetCurrencyExchangeRate(((BuyAction) action).ActionCurrency.Symbol,
                                                            portfolioBaseCurrencySymbol, ((BuyAction) action). ActionTime);

                                currenctCashRows = (DSIPAMS.DTCurrentCashRow[]) currentCash.Select(
                                                                                    string.Format(
                                                                                        "CASH_CURRENCY = '{0}'",
                                                                                        portfolioBaseCurrencySymbol));

                                if (currenctCashRows.Length == 1)
                                {
                                    MessageBox.Show(
                                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                    return;
                                }
                                else
                                {
                                    currenctCashRows[0].CASH_AMOUNT -= ((BuyAction) action).BuyPrice*
                                                                       ((BuyAction) action).Quantity*exchangeRate*
                                                                       stockPriceToActionRate;
                                    value = ((BuyAction)action).BuyPrice *
                                            ((BuyAction)action).Quantity * exchangeRate *
                                            stockPriceToActionRate;
                                }
                            }
                        }
                        transactions.AddDTFullTransactionsRow(currentInvestmentHouse, "Buy",
                                                              ((BuyAction)action). ActionTime,
                                                              ((BuyAction)action).StockInfo.Name,
                                                              ((BuyAction)action).StockInfo.Symbol,
                                                              ((BuyAction)action).Quantity,
                                                              ((BuyAction)action).BuyPrice,
                                                              ((BuyAction)action).StockInfo.StockCurrency.Symbol,
                                                              ((BuyAction)action).ActionCurrency.Symbol,
                                                              exchangeRate,
                                                              0,
                                                              0,
                                                              0,
                                                              empt,
                                                              0,
                                                              0,
                                                              0,
                                                              0, 0, 0, value, currenctCashRows[0].CASH_AMOUNT);


                        //add the new stocks to the portfolio. if stock already exists in portfolio - add, otherwise 
                        //create the stock in portfolio and add. 
                        if (currentStocks.Select(string.Format("STOCK_SYMBOL = '{0}'", ((BuyAction)action).StockInfo.Symbol)).Length == 0)
                        {
                            currentStocks.AddDTCurrentStocksRow(((BuyAction)action).StockInfo.Symbol, ((BuyAction)action).StockInfo.Name, ((BuyAction)action).Quantity);
                        }
                        else
                        {
                            currentStocks.Select(string.Format("STOCK_SYMBOL = '{0}'", ((BuyAction)action).StockInfo.Symbol))[0]["STOCK_QUANTITY"] = Convert.ToInt32(currentStocks.Select(string.Format("STOCK_SYMBOL = '{0}'", ((BuyAction)action).StockInfo.Symbol))[0]["STOCK_QUANTITY"]) + ((BuyAction)action).Quantity;
                        }
                        break;

                        #endregion

                        #region Perform Sell

                    case ActionTypes.Sell:
                        //remove the stocks from the stocks table or update the stock row;
                        //add the cash from the cash table.
                        //if there is a cash of the correct type - add to it, other - create the cash in the table 
                       
                        //when selling the stock.
                        //the action currency is set by the user. the currency can be the same of the stock. or different.
                        //if the currency exists in the portfolio, we can add to it. otherwise, 
                        //we need to add it. If the action is different from the stock, we need to translate the price for the action with
                        //the exchange rate tables. 

                        //reduce the stock from the portfolio - if none left, remove the line 
                        DSIPAMS.DTCurrentStocksRow[] stocks =
                            (DSIPAMS.DTCurrentStocksRow[])currentStocks.Select(
                                                              string.Format("STOCK_SYMBOL = '{0}'", ((SellAction)action).StockInfo.Symbol));

                        if (stocks.Length == 0)
                        {
                            MessageBox.Show(
                                "Error evaluating portfolio.\nOne or more tables are invalid.\nCannot sell a stock you dont own...",
                                "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            return;
                        }
                        else if (stocks.Length > 1)
                        {
                            MessageBox.Show(
                                "Error evaluating portfolio.\nOne or more tables are invalid.",
                                "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            return;
                        }
                        else
                        {
                            stocks[0].STOCK_QUANTITY -= ((SellAction)action).Quantity;
                        }



                        currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                           string.Format("CASH_CURRENCY = '{0}'", ((SellAction)action).ActionCurrency.Symbol));

                        //no need to exchange the stock price to the action's currency
                        if (((SellAction)action).ActionCurrency.Symbol == ((SellAction)action).StockInfo.StockCurrency.Symbol)
                        {
                            //the type of cash the action is taken in exists in the cash table.
                            if (currenctCashRows.Length == 1)
                            {
                                currenctCashRows[0].CASH_AMOUNT += ((SellAction)action).SellValue * ((SellAction)action).Quantity;
                            }
                            else //this type of cash does not exist, need to add this type to the table
                            {
                                currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                                   string.Format("CASH_CURRENCY = '{0}'", portfolioBaseCurrencySymbol));

                                if (currenctCashRows.Length != 1)
                                {
                                    MessageBox.Show(
                                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                    return;
                                }
                                else 
                                {
                                    
                                    exchangeRate = GetCurrencyExchangeRate(((SellAction)action).StockInfo.StockCurrency.Symbol, portfolioBaseCurrencySymbol, ((SellAction)action). ActionTime);

                                    //currentCash.AddDTCurrentCashRow(((SellAction) action).ActionCurrency.Symbol,
                                    //                                ((SellAction)action).SellValue * ((SellAction)action).Quantity * exchangeRate);
                                    value = ((SellAction)action).SellValue * ((SellAction)action).Quantity * exchangeRate;
                                    currenctCashRows[0].CASH_AMOUNT += value;
                                }
                            }
                        }
                        else //the action is taken in a different currency then the stock, need to translate the stock price to the action currency
                        {
                            double stockPriceToActionRate =
                                GetCurrencyExchangeRate(((SellAction)action).ActionCurrency.Symbol,
                                                        ((SellAction)action).StockInfo.StockCurrency.Symbol,
                                                        ((SellAction)action). ActionTime);


                            //the type of cash the action is taken in exists in the cash table.
                            if (currenctCashRows.Length == 1)
                            {
                                //the price now is the price in the stocks currency, we need to change it with the rate we found
                                currenctCashRows[0].CASH_AMOUNT += ((SellAction)action).SellValue *
                                                                   ((SellAction)action).Quantity * stockPriceToActionRate;
                                value = ((SellAction)action).SellValue *
                                        ((SellAction)action).Quantity * stockPriceToActionRate;
                            }
                            else //this type of cash does not exist - need to add it as before
                            {

                            
                                currentCash.AddDTCurrentCashRow(((SellAction)action).ActionCurrency.Symbol,
                                                                ((SellAction)action).SellValue * ((SellAction)action).Quantity *
                                                                stockPriceToActionRate);
                                value = ((SellAction) action).SellValue*((SellAction) action).Quantity*
                                        stockPriceToActionRate;
                            }
                        }

                        transactions.AddDTFullTransactionsRow(currentInvestmentHouse, "Sell",
                                                              ((SellAction)action). ActionTime,
                                                              ((SellAction)action).StockInfo.Name,
                                                              ((SellAction)action).StockInfo.Symbol,
                                                              ((SellAction)action).Quantity,
                                                              ((SellAction)action).SellValue,
                                                              ((SellAction)action).StockInfo.StockCurrency.Symbol,
                                                              ((SellAction)action).ActionCurrency.Symbol,
                                                              exchangeRate,
                                                              0,
                                                              0,
                                                              0,
                                                              empt,
                                                              0,
                                                              0,
                                                              0,
                                                              0, 0, 0,value, currenctCashRows[0].CASH_AMOUNT);

                       
                        break;

                        #endregion

                        #region Perform action-related commissions (Sell/Buy commissions)

                    case ActionTypes.Commission:
                        //check the date time for the commission action
                        //get the currency of the commission and the currency of the tik

                        exchangeRate =
                            GetCurrencyExchangeRate(((CommissionAction)action).ActionCurrency.Symbol,
                                                    portfolioBaseCurrencySymbol, ((CommissionAction)action). ActionTime);

                        
                            //when paying a commission, the fee can be in either the portfolio currency or other. 
                            //if the fee is in the portfolio currency - all we need is to calculate the fee according to the type
                            //and reduce it from the appropriate cash. 
                            //otherwise, if the fee is in another currency, we first need to caluculate it in the original currency,
                            //and then, if we have the currency in the portfolio - reduce from it, or exchange to the base currency.

                            currenctCashRows = (DSIPAMS.DTCurrentCashRow[]) currentCash.Select(
                                                                                string.Format("CASH_CURRENCY = '{0}'",
                                                                                              ((CommissionAction) action)
                                                                                                  .ActionCurrency.Symbol));

                            //the type of cash the action is taken in exists in the cash table.
                            if (currenctCashRows.Length == 1)
                            {
                                currenctCashRows[0].CASH_AMOUNT -= ((CommissionAction) action).Amount;
                                value = ((CommissionAction) action).Amount;
                            }
                            else
                                //this type of cash does not exist, the portfolio cash MUST exist in the folder or we have an exception
                            {
                                currenctCashRows = (DSIPAMS.DTCurrentCashRow[]) currentCash.Select(
                                                                                    string.Format(
                                                                                        "CASH_CURRENCY = '{0}'",
                                                                                        portfolioBaseCurrencySymbol));

                                if (currenctCashRows.Length != 1)
                                {
                                    MessageBox.Show(
                                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                    return;
                                }
                                else
                                {
                                    value = ((CommissionAction) action).Amount*exchangeRate;

                                    currenctCashRows[0].CASH_AMOUNT -= ((CommissionAction) action).Amount*exchangeRate;
                                }
                            }

                            transactions.AddDTFullTransactionsRow(currentInvestmentHouse,
                                                                  ((CommissionAction) action).ChargeName.ToString(),
                                                                  DateTime.MinValue,
                                                                  empt,
                                                                  empt,
                                                                  0,
                                                                  0,
                                                                  empt,
                                                                  ((CommissionAction) action).ActionCurrency.Symbol,
                                                                  exchangeRate,
                                                                  0,
                                                                  ((CommissionAction) action).Rate,
                                                                  0,
                                                                  empt,
                                                                  ((CommissionAction) action).Min,
                                                                  ((CommissionAction) action).Max,
                                                                  ((CommissionAction) action).PerStock,
                                                                  ((CommissionAction) action).Periodical,
                                                                  ((CommissionAction) action).Amount, 0, value, currenctCashRows[0].CASH_AMOUNT);
                       

                        break;
                    #endregion
                    #region Management

                    case ActionTypes.ManagingFee:

                        //check the date time for the commission action
                        //get the currency of the commission and the currency of the tik

                        exchangeRate =
                            GetCurrencyExchangeRate(((ManagingFeeAction)action).ActionCurrency.Symbol,
                                                    portfolioBaseCurrencySymbol, ((ManagingFeeAction)action).ActionTime);


                        //when paying a commission, the fee can be in either the portfolio currency or other. 
                        //if the fee is in the portfolio currency - all we need is to calculate the fee according to the type
                        //and reduce it from the appropriate cash. 
                        //otherwise, if the fee is in another currency, we first need to caluculate it in the original currency,
                        //and then, if we have the currency in the portfolio - reduce from it, or exchange to the base currency.

                        currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                            string.Format("CASH_CURRENCY = '{0}'",
                                                                                          ((ManagingFeeAction)action)
                                                                                              .ActionCurrency.Symbol));

                        //the type of cash the action is taken in exists in the cash table.
                        if (currenctCashRows.Length == 1)
                        {
                            //can use the value as is
                            if (((ManagingFeeAction)action).Amount != -1)
                            {
                                currenctCashRows[0].CASH_AMOUNT -= ((ManagingFeeAction)action).Amount;
                                value = ((ManagingFeeAction)action).Amount;
                            }
                            else //need to calculate the value
                            {
                                //currently supporting ONLY rate - need to add also by stock (see how many stocks are helf by this 
                                //investment house and caculate for them)
                                if(((ManagingFeeAction)action).Rate != 0)
                                {
                                    value = currenctCashRows[0].CASH_AMOUNT*((ManagingFeeAction) action).Rate;
                                    if (value > ((ManagingFeeAction)action).Max)
                                        value = ((ManagingFeeAction) action).Max;
                                    if (value < ((ManagingFeeAction)action).Min)
                                        value = ((ManagingFeeAction) action).Min;

                                    currenctCashRows[0].CASH_AMOUNT -= value;
                                }
                                else
                                {
                                    MessageBox.Show(
                                    "Error evaluating portfolio.\nAction does not contain proper data (Managing Fee).",
                                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                    return;
                                }

                            }
                        }
                        else
                        //this type of cash does not exist, the portfolio cash MUST exist in the folder or we have an exception
                        {
                            currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                                string.Format(
                                                                                    "CASH_CURRENCY = '{0}'",
                                                                                    portfolioBaseCurrencySymbol));

                            if (currenctCashRows.Length != 1)
                            {
                                MessageBox.Show(
                                    "Error evaluating portfolio.\nOne or more tables are invalid.",
                                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                return;
                            }
                            else
                            {

                                if (((ManagingFeeAction)action).Rate != 0)
                                {
                                    value = currenctCashRows[0].CASH_AMOUNT * ((ManagingFeeAction)action).Rate * exchangeRate;
                                    if (value > ((ManagingFeeAction)action).Max)
                                        value = ((ManagingFeeAction)action).Max;
                                    if (value < ((ManagingFeeAction)action).Min)
                                        value = ((ManagingFeeAction)action).Min;
                                   
                                    currenctCashRows[0].CASH_AMOUNT -= value;
                                }
                                else
                                {
                                    MessageBox.Show(
                                    "Error evaluating portfolio.\nAction does not contain proper data (Managing Fee).",
                                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                    return;
                                }
                            }
                        }
                        ((ManagingFeeAction)action).Amount = value;

                        transactions.AddDTFullTransactionsRow(currentInvestmentHouse,
                                                              ((ManagingFeeAction)action).ActionType.ToString(),
                                                              DateTime.MinValue,
                                                              empt,
                                                              empt,
                                                              0,
                                                              0,
                                                              empt,
                                                              ((ManagingFeeAction)action).ActionCurrency.Symbol,
                                                              exchangeRate,
                                                              0,
                                                              ((ManagingFeeAction)action).Rate,
                                                              0,
                                                              empt,
                                                              ((ManagingFeeAction)action).Min,
                                                              ((ManagingFeeAction)action).Max,
                                                              ((ManagingFeeAction)action).PerStock,
                                                              ((ManagingFeeAction)action).Periodical,
                                                              ((ManagingFeeAction)action).Amount, 0, value, currenctCashRows[0].CASH_AMOUNT);
                       



                        //transactions.AddDTFullTransactionsRow("RTL", "COMMISION", "7:11:2007", "MOSHE", "BUZAGLO", "11", "7",
                        //                   "NIS", "USD",
                        //                   empt, empt, empt, empt, empt, empt, empt, empt, "13");

                        break;

                        #endregion

                        #region Perform Taxes
                        
                    case ActionTypes.Tax:
                        //check the date time for the commission action
                        //get the currency of the commission and the currency of the tik

                        exchangeRate =
                            GetCurrencyExchangeRate(((TaxAction)action).ActionCurrency.Symbol,
                                                    portfolioBaseCurrencySymbol, ((TaxAction)action). ActionTime);


                        //when paying a commission, the fee can be in either the portfolio currency or other. 
                        //if the fee is in the portfolio currency - all we need is to calculate the fee according to the type
                        //and reduce it from the appropriate cash. 
                        //otherwise, if the fee is in another currency, we first need to caluculate it in the original currency,
                        //and then, if we have the currency in the portfolio - reduce from it, or exchange to the base currency.

                        currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                           string.Format("CASH_CURRENCY = '{0}'", ((TaxAction)action).ActionCurrency.Symbol));

                        //the type of cash the action is taken in exists in the cash table.
                        if (currenctCashRows.Length == 1)
                        {
                            currenctCashRows[0].CASH_AMOUNT -= ((TaxAction)action).Amount;
                            value = ((TaxAction)action).Amount;
                        }
                        else //this type of cash does not exist, the portfolio cash MUST exist in the folder or we have an exception
                        {
                            currenctCashRows = (DSIPAMS.DTCurrentCashRow[])currentCash.Select(
                                                                               string.Format("CASH_CURRENCY = '{0}'", portfolioBaseCurrencySymbol));

                            if (currenctCashRows.Length != 1)
                            {
                                MessageBox.Show(
                                    "Error evaluating portfolio.\nOne or more tables are invalid.",
                                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                return;
                            }
                            else
                            {
                                value = ((TaxAction)action).Amount * exchangeRate;

                                currenctCashRows[0].CASH_AMOUNT -= ((TaxAction)action).Amount * exchangeRate;
                            }
                        }
 
                        transactions.AddDTFullTransactionsRow(currentInvestmentHouse,
                                                              ((TaxAction) action).TaxOn.ToString(),
                                                              ((TaxAction)action). ActionTime,
                                                              empt,
                                                              empt,
                                                              0,
                                                              0,
                                                              empt,
                                                              ((TaxAction)action).ActionCurrency.Symbol,
                                                              exchangeRate, 
                                                              ((TaxAction)action).Rate,
                                                              0,
                                                              0,
                                                              ((TaxAction)action).CountrySymbol,
                                                              0,
                                                              0,
                                                              0,
                                                              0, 0, ((TaxAction)action).Amount, value, currenctCashRows[0].CASH_AMOUNT);



                      
                        break;

                        #endregion



                }
            }

            #endregion
            
            //update data grid views
            transactions.AcceptChanges();
            evaluationEditor.dgvFullTransactionsDisplay.DataSource = transactions;
            evaluationEditor.dgvFullTransactionsDisplay.Refresh();

            currentCash.AcceptChanges();
            evaluationEditor.dgvCurrentCurrenciesStatus.DataSource = currentCash;
            evaluationEditor.dgvCurrentCurrenciesStatus.Refresh();

            currentStocks.AcceptChanges();
            evaluationEditor.dgvCurrentStocksStatus.DataSource = currentStocks;
            evaluationEditor.dgvCurrentStocksStatus.Refresh();
        }

        /// <summary>
        /// Gets the periodical commissions between the start date  and end dateof the portfolio
        /// which are given by the user.
        /// </summary>
        /// <returns>a list of commissions</returns>
        private List<IAction> GetPeriodicalCommissions()
        {
            List<IAction> periodicalChargesList = new List<IAction>();
            if(startDateTime > endDateTime)
            {
                MessageBox.Show(
                  "Error evaluating portfolio.\nEvaluation Start date is after End date.",
                  "Portfolio TimeSpan Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return null;
            }
            else
            {
                DSIPAMS.DTChargesRow[] periodicalCharges =
                    (DSIPAMS.DTChargesRow[])
                    dsipams.DTCharges.Select(string.Format("CHARGE_START_DATE >= '{0}'",startDateTime));

                foreach( DSIPAMS.DTChargesRow charge in periodicalCharges)
                {
                    if(charge.CHARGE_MONTHLY_PERIOD != 0)
                    {
                        //currenctly the commission is added by X-months * 30-days. 
                        //more accurate commission will be using the date in the month and add by it. 
                        //for that we need to make sure there IS that date in the month (31st for example) 
                        TimeSpan timeSpan = new TimeSpan(31*charge.CHARGE_MONTHLY_PERIOD,0,0,0);
                        DateTime commissionDate = startDateTime + timeSpan;
                        while(commissionDate <= endDateTime)
                        {
                            Currency currency;
                            DSIPAMS.DTInvestmentManagementHousesRow[] investmentHouses =
                                (DSIPAMS.DTInvestmentManagementHousesRow[])
                                dsipams.Tables["DTInvestmentManagementHouses"].Select(
                                    string.Format("INVESTMENT_HOUSE_CODE = '{0}'", charge.CHARGE_IMH_SYMBOL));

                            if (investmentHouses.Length != 1)
                            {
                                MessageBox.Show(
                                    "Error evaluating portfolio.\nOne or more tables are invalid.",
                                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                                return null;
                            }
                            else
                            {
                                currency = GetCurrencyByCountry(investmentHouses[0].INVESTMENT_HOUSE_COUNTRY_CODE);
                            }


                            double value = -1; 
                            //the value cannot be evaluated at this point since the commission is sometime
                            //protfolio-value-dependendt - so when executing this type of commission - we need to evaluate the managing fee
                            //according to the portfolio value at the time

                            //rate commission 
                            if (charge.CHARGE_VALUE != 0) value = charge.CHARGE_VALUE;
                            periodicalChargesList.Add(new ManagingFeeAction(currency, 
                                                     commissionDate, 
                                                     value,
                                                     charge.CHARGE_RATE,
                                                     charge.CHARGE_MINIMAL,
                                                     charge.CHARGE_MAXIMAL, 
                                                     charge.CHARGE_PER_STOCK,
                                                     charge.CHARGE_MONTHLY_PERIOD));

                            commissionDate += timeSpan;
                        }

                    }
                }
            }
            periodicalChargesList.Sort();
            return periodicalChargesList;
        }


        /// <summary>
        /// Gets the portfolio base currency symbol.
        /// </summary>
        /// <returns>a string - portfolio base currency symbol.</returns>
        private string GetPortfolioBaseCurrencySymbol()
        {
            string portfolioBaseCurrencySymbol;
            if (currentInvestmentHouse == "None Selected" || currentInvestmentHouse == string.Empty)
            {
                MessageBox.Show(
                    "Error evaluating portfolio.\nThe portfolio cannot be evaluated without choosing an Investment House.",
                    "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return string.Empty;
            }
            else
            {
                DSIPAMS.DTInvestmentManagementHousesRow[] investmentHouses =
                    (DSIPAMS.DTInvestmentManagementHousesRow[])dsipams.Tables[TablesNames.DTInvestmentManagementHouses].Select(
                                                                   string.Format("INVESTMENT_HOUSE_CODE = '{0}'", currentInvestmentHouse));
                //table error, more then one house with specific name.
                if (investmentHouses.Length != 1)
                {
                    MessageBox.Show(
                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return string.Empty;
                }
                else
                {
                    DSIPAMS.DTCountriesRow[] countries =
                        (DSIPAMS.DTCountriesRow[])
                        dsipams.Tables[TablesNames.DTCountries].Select(
                            string.Format("COUNTRY_SYMBOL = '{0}'", investmentHouses[0].INVESTMENT_HOUSE_COUNTRY_CODE));
                    if (countries.Length != 1)
                    {
                        MessageBox.Show(
                            "Error evaluating portfolio.\nOne or more tables are invalid.",
                            "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        return string.Empty;
                    }
                    else
                    {
                        portfolioBaseCurrencySymbol = countries[0].COUNTRY_CURRENCY_SYMBOL;
                    }
                }
            }
            return portfolioBaseCurrencySymbol;
        }


        /// <summary>
        /// Gets the list of taxes applied after a buy action.
        /// </summary>
        /// <param name="action">The buy action.</param>
        /// <returns>The list of valid taxes if any. If an error occures - returns null value.</returns>
        private List<IAction> GetAfterBuyTaxes(BuyAction action)
        {
            //TODO: Implement the taxes
            return new List<IAction>();
        }

        private Currency GetTransactionCurrency(DSIPAMS.DTQuantityTransactionsRow row)
        {
            DSIPAMS.DTCurrenciesRow[] currencies =
                (DSIPAMS.DTCurrenciesRow[])dsipams.DTCurrencies.Select(string.Format("CURRENCY_SYMBOL = '{0}'", row.QUANTITY_TRANSACTION_CURRENCY_SYMBOL));
           
            if (currencies.Length != 1)
            {
                MessageBox.Show(
                    "Error evaluating portfolio.\nOne or more tables are invalid.",
                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                throw new Exception("Tables consistency error!!");
            }
            return new Currency(currencies[0].CURRENCY_NAME, currencies[0].CURRENCY_SYMBOL, currencies[0].CURRENCY_COUNTY);
        }

        #region Utility Methods

        /// <summary>
        /// Gets the list of taxes applied after a sell action.
        /// </summary>
        /// <param name="sellAction">The sell action.</param>
        /// <returns>The list of valid taxes if any. If an error occures - returns null value.</returns>
        private List<IAction> GetAfterSellTaxes(SellAction sellAction)
        {
            List<IAction> afterSellTaxesList = new List<IAction>();

            #region Get the taxes applied in the stock's country

            //finding the stock exchange row          
            string stockCode = string.Format("STOCK_SYMBOL = '{0}'", sellAction.StockInfo.Symbol);
            DSIPAMS.DTStocksRow[] validStocks = (DSIPAMS.DTStocksRow[])stocksEditor.stocks.Select(stockCode);
            if (validStocks.Length != 1)
            {
                MessageBox.Show("Error evaluating portfolio.\nOne or more tables are invalid.",
                                "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return null;
            }
            string stockSECode = validStocks[0].STOCK_SE_CODE;

            //finding the country code  
            string countryExpression = string.Format("STOCK_EXCHANGE_SYMBOL = '{0}'", stockSECode);
            DSIPAMS.DTStockExchangesRow[] validStockExchanges = (DSIPAMS.DTStockExchangesRow[])stockExchangesEditor.stockExchanges.Select(countryExpression);
            if (validStockExchanges.Length != 1)
            {
                MessageBox.Show("Error evaluating portfolio.\nOne or more tables are invalid.",
                                "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return null;
            }
            string countryCode = validStockExchanges[0].STOCK_EXCHANGE_COUNTRY_SYMBOL;

            string taxesExpression = string.Format("TAX_COUNTRY_SYMBOL = '{0}' AND TAX_START_DATE <= '{1}' AND TAX_END_DATE >= '{2}'",
                                                   countryCode, sellAction.ActionTime, sellAction.ActionTime);

            DSIPAMS.DTTaxesRow[] validTaxes = (DSIPAMS.DTTaxesRow[])taxesEditor.taxes.Select(taxesExpression);

            if (validTaxes.Length != 0)
            {
                foreach (DSIPAMS.DTTaxesRow tax in validTaxes)
                {
                    if (string.Format(tax.TAX_ON).ToUpper().Contains("SELL"))
                    {
                        double value = tax.TAX_RATE * sellAction.SellValue * sellAction.Quantity;// / (100.00);
                        afterSellTaxesList.Add(new TaxAction(GetCurrencyByCountry(countryCode), sellAction.ActionTime, value, tax.TAX_RATE, TaxAction.TaxType.SellTax, countryCode));
                    }
                }
            }

            #endregion

            #region Get the taxes applied in the Investment House country

          
            string portfolioCurrencySymbol = GetPortfolioBaseCurrencySymbol();
            if(portfolioCurrencySymbol == string.Empty)
            {
                MessageBox.Show("Error evaluating portfolio.\nMissing portfolio currency (Investment House not selected).",
                                "No Investment House error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return null;
            }
            Currency portfolioCurrency = GetCurrencyBySymbol(portfolioCurrencySymbol);
            if(portfolioCurrency ==null)
            {
                MessageBox.Show("Error evaluating portfolio.\nMissing portfolio currency (Investment House not selected).",
                                "No Investment House error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return null;
            }
           
            taxesExpression = string.Format("TAX_COUNTRY_SYMBOL = '{0}' AND TAX_START_DATE <= '{1}' AND TAX_END_DATE >= '{2}'",
                                            portfolioCurrency.Country, sellAction.ActionTime, sellAction.ActionTime);

            validTaxes = (DSIPAMS.DTTaxesRow[])taxesEditor.taxes.Select(taxesExpression);

            if (validTaxes.Length != 0)
            {
                foreach (DSIPAMS.DTTaxesRow tax in validTaxes)
                {
                    if (portfolioCurrency.Country != sellAction.StockInfo.StockCurrency.Country) //prevent double taxing 
                    {
                        if (string.Format(tax.TAX_ON).ToUpper().Contains("SELL"))
                        {
                            double value = tax.TAX_RATE * sellAction.SellValue * sellAction.Quantity ;// / (100.00);
                            afterSellTaxesList.Add(new TaxAction(GetCurrencyByCountry(portfolioCurrency.Country), sellAction.ActionTime, value, tax.TAX_RATE, TaxAction.TaxType.SellTax, portfolioCurrency.Country));
                        }
                    }
                }
            }
          

            #endregion


            return afterSellTaxesList;
        }


        /// <summary>
        /// Gets a list of commissions that need to be charged after a given buy action.
        /// </summary>
        /// <param name="buyAction">The buy action.</param>
        /// <returns>A list of commisions</returns>
        public List<IAction> GetAfterBuyCommissions(BuyAction buyAction)
        {
            List<IAction> afterBuyList = new List<IAction>();

            string chargesExpression = string.Format("CHARGE_START_DATE <= '{0}' AND CHARGE_END_DATE >= '{1}'",
                                                     buyAction.ActionTime, buyAction.ActionTime);
            DSIPAMS.DTChargesRow[] validCharges = (DSIPAMS.DTChargesRow[])chargesEditor.charges.Select(chargesExpression);

           

            foreach (DSIPAMS.DTChargesRow charge in validCharges)
            {
                Currency currency;
                DSIPAMS.DTInvestmentManagementHousesRow[] investmentHouses =
                    (DSIPAMS.DTInvestmentManagementHousesRow[])
                    dsipams.Tables["DTInvestmentManagementHouses"].Select(
                        string.Format("INVESTMENT_HOUSE_CODE = '{0}'", charge.CHARGE_IMH_SYMBOL));

                if (investmentHouses.Length != 1)
                {
                    MessageBox.Show(
                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return null;
                }
                else
                {
                    //currency = GetCurrencyByCountry(investmentHouses[0].INVESTMENT_HOUSE_COUNTRY_CODE);
                    currency = GetCurrencyBySymbol(charge.CHARGE_CURRENCY);
                }

                double value = 0;

                //rate commission 
                //need to evaluate the value of the transaction and multiply the rate with the value.
                //the value is decided by the price*quantity 
                //we do not care at this point for the currency since it does not matter. 
                if (charge.CHARGE_RATE != 0) value = buyAction.BuyPrice * buyAction.Quantity * charge.CHARGE_RATE;// / (100.00) ;
                else if (charge.CHARGE_PER_STOCK != 0) value = charge.CHARGE_PER_STOCK * buyAction.Quantity;
                else if (charge.CHARGE_VALUE != 0) value = charge.CHARGE_VALUE; 

                //fix the charge between the boundries
                if (value < charge.CHARGE_MINIMAL) value = charge.CHARGE_MINIMAL;
                if (charge.CHARGE_MAXIMAL != 0 && value > charge.CHARGE_MAXIMAL) value = charge.CHARGE_MAXIMAL;

                if (charge.CHARGE_TYPE == "Buy Commission")
                {
                    afterBuyList.Add(new CommissionAction(currency, buyAction.ActionTime, value, CommissionAction.ChargeType.BuyCommission, charge.CHARGE_RATE, charge.CHARGE_MINIMAL, charge.CHARGE_MAXIMAL, charge.CHARGE_PER_STOCK, charge.CHARGE_MONTHLY_PERIOD));
                }
                if (charge.CHARGE_TYPE == "Broker Commission")
                {
                    afterBuyList.Add(new CommissionAction(currency, buyAction.ActionTime, value, CommissionAction.ChargeType.BrokerCommission, charge.CHARGE_RATE, charge.CHARGE_MINIMAL, charge.CHARGE_MAXIMAL, charge.CHARGE_PER_STOCK, charge.CHARGE_MONTHLY_PERIOD));
                }
            }
            return afterBuyList;
        }

        /// <summary>
        /// Gets the currency by the currency symbol.
        /// </summary>
        /// <param name="currencySymbol">The currency symbol.</param>
        /// <returns>the Currency</returns>
        private Currency GetCurrencyBySymbol(string currencySymbol)
        {
            if(currencySymbol == string.Empty) return null;
            DSIPAMS.DTCurrenciesRow[] currencies =
                (DSIPAMS.DTCurrenciesRow[])
                dsipams.DTCurrencies.Select(string.Format("CURRENCY_SYMBOL = '{0}'", currencySymbol));
            if(currencies.Length != 1)
            {
                MessageBox.Show(
                    "Error evaluating portfolio.\nOne or more tables are invalid. (Currencies)",
                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return null;
            }
            else
            {
                return new Currency(currencies[0].CURRENCY_NAME, currencies[0].CURRENCY_SYMBOL, currencies[0].CURRENCY_COUNTY);
            }
        }

        /// <summary>
        /// Gets a list of commissions that need to be charged after a given sell action.
        /// </summary>
        /// <param name="sellAction">The sell action.</param>
        /// <returns>A list of commisions</returns>
        public List<IAction> GetAfterSellCommissions(SellAction sellAction)
        {
            List<IAction> afterSellList = new List<IAction>();
            string chargesExpression = string.Format("CHARGE_START_DATE <= '{0}' AND CHARGE_END_DATE >= '{1}' AND CHARGE_TYPE = '{2}'",
                                                     sellAction.ActionTime, sellAction.ActionTime, "Sell Commission");
            DSIPAMS.DTChargesRow[] validCharges = (DSIPAMS.DTChargesRow[])chargesEditor.charges.Select(chargesExpression);



            foreach (DSIPAMS.DTChargesRow charge in validCharges)
            {
                Currency currency;
                DSIPAMS.DTInvestmentManagementHousesRow[] investmentHouses =
                    (DSIPAMS.DTInvestmentManagementHousesRow[])
                    dsipams.Tables["DTInvestmentManagementHouses"].Select(
                        string.Format("INVESTMENT_HOUSE_CODE = '{0}'", charge.CHARGE_IMH_SYMBOL));

                if (investmentHouses.Length != 1)
                {
                    MessageBox.Show(
                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return null;
                }
                else
                {
                    currency = GetCurrencyByCountry(investmentHouses[0].INVESTMENT_HOUSE_COUNTRY_CODE);
                }

                double value = 0;

                //rate commission 
                //need to evaluate the value of the transaction and multiply the rate with the value.
                //the value is decided by the price*quantity 
                //we do not care at this point for the currency since it does not matter. 
                if (charge.CHARGE_RATE != 0) value = sellAction.SellValue * sellAction.Quantity * charge.CHARGE_RATE;// / (100.00);
                else if (charge.CHARGE_PER_STOCK != 0) value = charge.CHARGE_PER_STOCK * sellAction.Quantity;
                else if (charge.CHARGE_VALUE != 0) value = charge.CHARGE_VALUE;

                //fix the charge between the boundries
                if (value < charge.CHARGE_MINIMAL) value = charge.CHARGE_MINIMAL;
                if (charge.CHARGE_MAXIMAL != 0 && value > charge.CHARGE_MAXIMAL) value = charge.CHARGE_MAXIMAL;

                if (charge.CHARGE_TYPE == "Sell Commission")
                {
                    afterSellList.Add(new CommissionAction(currency, sellAction.ActionTime, value, CommissionAction.ChargeType.SellCommission, charge.CHARGE_RATE, charge.CHARGE_MINIMAL, charge.CHARGE_MAXIMAL, charge.CHARGE_PER_STOCK, charge.CHARGE_MONTHLY_PERIOD));
                }
                else if (charge.CHARGE_TYPE == "Broker Commission")
                {
                    afterSellList.Add(new CommissionAction(currency, sellAction.ActionTime, value, CommissionAction.ChargeType.BrokerCommission, charge.CHARGE_RATE, charge.CHARGE_MINIMAL, charge.CHARGE_MAXIMAL, charge.CHARGE_PER_STOCK, charge.CHARGE_MONTHLY_PERIOD));
                }
            }
            return afterSellList;
        }

        /// <summary>
        /// Gets the currency in the given country.
        /// </summary>
        /// <param name="countryCode">The country code.</param>
        /// <returns>A Currency</returns>
        private Currency GetCurrencyByCountry(string countryCode)
        {
            DSIPAMS.DTCountriesRow[] countries =
                (DSIPAMS.DTCountriesRow[])
                dsipams.Tables["DTCountries"].Select(string.Format("COUNTRY_SYMBOL = '{0}'", countryCode));
            if (countries.Length != 1)
            {
                MessageBox.Show(
                    "Error evaluating portfolio.\nOne or more tables are invalid.",
                    "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return null;
            }
            else
            {
                DSIPAMS.DTCurrenciesRow[] currencies =
                    (DSIPAMS.DTCurrenciesRow[])
                    dsipams.Tables["DTCurrencies"].Select(
                        string.Format("CURRENCY_COUNTY = '{0}'", countries[0].COUNTRY_SYMBOL));
                if(currencies.Length != 1)
                {
                    MessageBox.Show(
                        "Error evaluating portfolio.\nOne or more tables are invalid.",
                        "Tables consistency error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return null;
                }
                else
                    return new Currency(currencies[0].CURRENCY_NAME, currencies[0].CURRENCY_SYMBOL, currencies[0].CURRENCY_COUNTY);
                
            }
        }

        /// <summary>
        /// Calculates the charge.
        /// </summary>
        /// <param name="chargeRow">The row trom Charges table.</param>
        /// <param name="stockPrice">The stock price.</param>
        /// <param name="quantity">The quantity of the stocks in the action.</param>
        /// <returns>The charge amount </returns>
        public double CalculateCharge(DSIPAMS.DTChargesRow chargeRow, double stockPrice, int quantity)
        {
            double value, min, max;
                
            if (!chargeRow.IsCHARGE_VALUENull())
            {
                value = Convert.ToDouble(chargeRow.CHARGE_VALUE);
            }
            else
            {
                if (!chargeRow.IsCHARGE_RATENull())
                {
                    value = (Convert.ToDouble(chargeRow.CHARGE_RATE)) * stockPrice*quantity;
                }
                else
                {
                    if (!chargeRow.IsCHARGE_PER_STOCKNull())
                    {
                        value = (Convert.ToDouble(chargeRow.CHARGE_PER_STOCK)) * quantity;
                    }
                    else value = 0;
                }
            }
      
            min = Convert.ToDouble(chargeRow.CHARGE_MINIMAL);
            max = Convert.ToDouble(chargeRow.CHARGE_MAXIMAL);
         
            if ( value < min) return min;
            if ( value > max) return max;
            return value;
        }



        /// <summary>
        /// Gets the currency exchange rate.
        /// </summary>
        /// <param name="fromCurrencySymbol">From currency symbol.</param>
        /// <param name="toCurrencySymbol">To currency symbol.</param>
        /// <param name="exchangeDate">The exchange date.</param>
        /// <returns>The exchange rate</returns>
        private double GetCurrencyExchangeRate(string fromCurrencySymbol, string toCurrencySymbol, DateTime exchangeDate)
        {
            string exchangeRateExpression =
                string.Format(
                    "CURRENCY_EXCHANGE_RATE_FROM_SYMBOL = '{0}' AND CURRENCY_EXCHANGE_RATE_TO_SYMBOL = '{1}'",
                    fromCurrencySymbol, toCurrencySymbol);

            if (fromCurrencySymbol == toCurrencySymbol) return 1;

            DSIPAMS.DTCurrencyExchangeRateRow[] rates =
                (DSIPAMS.DTCurrencyExchangeRateRow[]) dsipams.DTCurrencyExchangeRate.Select(exchangeRateExpression);

            if(rates == null || rates.Length == 0)
            {
                MessageBox.Show(
                    "Error evaluating portfolio.\nOne or more tables are invalid.\nNo valid exchange rates entered.",
                    "Missing data error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return -1;
            }
            else if (rates.Length == 1 )
            {
                if (rates[0].CURRENCY_EXCHANGE_RATE_DATE <= exchangeDate)
                {
                    return rates[0].CURRENCY_EXCHANGE_RATE_RATE;
                }
                else
                {
                    MessageBox.Show(
                        "Error evaluating portfolio.\nOne or more tables are invalid.\nNo valid exchange rates entered. (date error)",
                        "Missing data error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return -1;
                }
            }
            else
            {
               
                int rateIndex = 0;
                while(rateIndex < rates.Length)
                {

                    if(rates[rateIndex].CURRENCY_EXCHANGE_RATE_DATE == exchangeDate)
                    {
                        return rates[rateIndex].CURRENCY_EXCHANGE_RATE_RATE;
                    }
                    else if(rateIndex < rates.Length && rates[rateIndex].CURRENCY_EXCHANGE_RATE_DATE < exchangeDate && rates[rateIndex+1].CURRENCY_EXCHANGE_RATE_DATE > exchangeDate)
                    {
                        return rates[rateIndex].CURRENCY_EXCHANGE_RATE_RATE;
                    }
                    else if (rateIndex == rates.Length)
                    {
                        if (rates[rateIndex].CURRENCY_EXCHANGE_RATE_DATE < exchangeDate)
                        {
                            return rates[rateIndex].CURRENCY_EXCHANGE_RATE_RATE;
                        }
                        else
                        {
                            MessageBox.Show(
                                "Error evaluating portfolio.\nOne or more tables are invalid.\nNo valid exchange rates entered. (date error)",
                                "Missing data error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            return -1;
                        }
                    }

                    rateIndex++;
                }
            }
            MessageBox.Show(
                "Error evaluating portfolio.\nOne or more tables are invalid.\nNo valid exchange rates entered. (date error)",
                "Missing data error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            return -1;
          
        }



        /// <summary>
        /// Gets the stock currency.
        /// </summary>
        /// <param name="stockSymbol">The stock symbol.</param>
        /// <returns>a <see cref="Currency"/> Currency</returns>
        public Currency GetStockCurrency(string stockSymbol)
        {
            string SEExpression = string.Format("STOCK_SYMBOL = '{0}'", stockSymbol);
            DSIPAMS.DTStocksRow[] validStocks = (DSIPAMS.DTStocksRow[])stocksEditor.stocks.Select(SEExpression);
            string stockSECode = validStocks[0].STOCK_SE_CODE;

            //finding the country code  
            string countryExpression = string.Format("STOCK_EXCHANGE_SYMBOL = '{0}'", stockSECode);
            DSIPAMS.DTStockExchangesRow[] validStockExchanges = (DSIPAMS.DTStockExchangesRow[])stockExchangesEditor.stockExchanges.Select(countryExpression);
            string countryCode = validStockExchanges[0].STOCK_EXCHANGE_COUNTRY_SYMBOL;
        
            //finding the currency
            string currencyExpression = string.Format("COUNTRY_SYMBOL = '{0}'"  ,countryCode);
            DSIPAMS.DTCountriesRow[] validCountries = (DSIPAMS.DTCountriesRow[])countriesEditor.countries.Select(currencyExpression);
            string currencyCode = validCountries[0].COUNTRY_CURRENCY_SYMBOL;
            
            //finding the currency name
            string currencyCodeExpression = string.Format("CURRENCY_SYMBOL = '{0}'", currencyCode);
            DSIPAMS.DTCurrenciesRow[] validCurrencies = (DSIPAMS.DTCurrenciesRow[])currenciesEditor.currenciesTable.Select(currencyCodeExpression);
            string currencyName = validCurrencies[0].CURRENCY_NAME;
            Currency stockCurrency = new Currency(currencyName, currencyCode, countryCode);
            return stockCurrency;
        }


        /// <summary>
        /// Gets the stock name by its symbol.
        /// </summary>
        /// <param name="stockSymbol">The stock symbol.</param>
        /// <returns>a string - the stocks name</returns>
        private string GetStockNameBySymbol(string stockSymbol)
        {
            string expression = string.Format("STOCK_SYMBOL = '{0}'", stockSymbol);
            DataRow[] stocks = dsipams.Tables["DTStocks"].Select(expression);
            if(stocks.Length != 1)
            {
                throw new Exception("Stock Symbol not unique !");
            }
            return (stocks[0])["STOCK_NAME"].ToString();
        }

        /// <summary>
        /// Evaluates the stock's price on a given date.
        /// </summary>
        /// <param name="exchangeDate">The exchange date.</param>
        /// <param name="stockSymbol">The stock symbol.</param>
        /// <returns>The stock's evaluated price </returns>
        private double EvaluateStockPriceOnDate(DateTime exchangeDate, string stockSymbol)
        {
            //try getting the value on the exact date
            string fullExpression = 
                string.Format("QUOTE_STOCK_SYMBOL = '{0}' AND QUOTE_DATE = '{1}'", stockSymbol, exchangeDate);
            
            if (dsipams.Tables["DTQuotes"].Select(fullExpression).Length == 1)
                return Convert.ToDouble(dsipams.Tables["DTQuotes"].Select(fullExpression)[0]["QUOTE_RATE"]);

            //exact date was not found in quotes try evaluate
            
            DSIPAMS.DTQuotesRow quoteAfter = null;
            DSIPAMS.DTQuotesRow quoteBefore = null;
          
            string expression = string.Format("QUOTE_STOCK_SYMBOL = '{0}'",stockSymbol);
            
            foreach(DSIPAMS.DTQuotesRow quote in dsipams.Tables["DTQuotes"].Select(expression,"QUOTE_DATE ASC"))
            {
                //if (quote.QUOTE_DATE.Date.Subtract(exchangeDate.Date).Days > 0)
                if (quote.QUOTE_DATE.CompareTo(exchangeDate) > 0)
                {
                    quoteAfter = quote;
                    break;
                }
                quoteBefore = quote;
            }

            if (quoteBefore != null && quoteAfter != null)
            {
                TimeSpan datesGap = quoteAfter.QUOTE_DATE.Subtract(quoteBefore.QUOTE_DATE);
                double dailyChange = (quoteAfter.QUOTE_RATE - quoteBefore.QUOTE_RATE)/datesGap.Days;
                TimeSpan evaluatedSpan = exchangeDate.Subtract(quoteBefore.QUOTE_DATE);
                return (evaluatedSpan.Days*dailyChange) + quoteBefore.QUOTE_RATE;
            }
            else if(quoteBefore != null )
            {
                return quoteBefore.QUOTE_RATE;
            }
            else if( quoteAfter != null)
            {
                return quoteAfter.QUOTE_RATE;
            }
            else
            {
                return -1;
            }
        }



        #endregion


        #region Unused Methods

        /// <summary>
        /// Updates the information of the different tables editors.
        /// </summary>
        private void UpdateInformation()
        {
            //Currencies
            if (dsipams.DTCurrencies != null && dsipams.DTCurrencies.Rows.Count > 0)
            {
                currencyExchangeRatesEditor.comboBoxSelectOriginalCurrency.Items.Add("");
                currencyExchangeRatesEditor.comboBoxSelectTargetCurrency.Items.Add("");
                foreach (DSIPAMS.DTCurrenciesRow dr in dsipams.DTCurrencies.Rows)
                {
                    stocksEditor.comboBoxCurrencies.Items.Add(dr.CURRENCY_SYMBOL);
                    cashSetup.comboBoxCash.Items.Add(dr.CURRENCY_SYMBOL);
                    currencyExchangeRatesEditor.comboBoxSelectOriginalCurrency.Items.Add(dr["CURRENCY_SYMBOL"]);
                    currencyExchangeRatesEditor.comboBoxSelectTargetCurrency.Items.Add(dr["CURRENCY_SYMBOL"]);
                }
            }

            //Stocks
            if (dsipams.DTStocks != null && dsipams.DTStocks.Rows.Count > 0)
            {

                foreach (DataRow dr in dsipams.Tables["DTStocks"].Rows)
                {
                    quotesEditor.comboBoxSelectStock.Items.Add(dr["STOCK_SYMBOL"]);
                    transactionEditor.comboBoxStockSymbols.Items.Add(dr["STOCK_SYMBOL"]);
                }
            }

            //Stocks Exchange Rates
            if (dsipams.Tables["DTQuotes"] != null && dsipams.Tables["DTQuotes"].Rows.Count > 0)
            {
                quotesEditor.quotes.Clear();
                quotesEditor.quotes.Merge(dsipams.Tables["DTQuotes"]);
            }

            //Currencies Exchange Rates
            if (dsipams.Tables["DTCurrencyExchangeRate"] != null && dsipams.Tables["DTCurrencyExchangeRate"].Rows.Count > 0)
            {
                currencyExchangeRatesEditor.currencyExchangeRates.Clear();
                currencyExchangeRatesEditor.currencyExchangeRates.Merge(dsipams.Tables["DTCurrencyExchangeRate"]);
            }

            //Quantity Transactions
            if (dsipams.Tables["DTQuantityTransactions"] != null && dsipams.Tables["DTQuantityTransactions"].Rows.Count > 0)
            {
                transactionEditor.transactions.Clear();
                transactionEditor.transactions.Merge(dsipams.Tables["DTQuantityTransactions"]);
            }

            //Dividends 
            if (dsipams.Tables["DTDividends"] != null && dsipams.Tables["DTDividends"].Rows.Count > 0)
            {
                dividendsEditor.dividends.Clear();
                dividendsEditor.dividends.Merge(dsipams.Tables["DTDividends"]);
            }

            //Countries
            if (dsipams.Tables["DTCountries"] != null && dsipams.Tables["DTCountries"].Rows.Count > 0)
            {
                dividendsEditor.dividends.Clear();
                dividendsEditor.dividends.Merge(dsipams.Tables["DTCountries"]);
            }

            //StockExchanges
            if (dsipams.Tables["DTStockExchanges"] != null && dsipams.Tables["DTStockExchanges"].Rows.Count > 0)
            {
                dividendsEditor.dividends.Clear();
                dividendsEditor.dividends.Merge(dsipams.Tables["DTStockExchanges"]);
            }

        }

        private void ReSyncInformation()
        {
            //retrieve the data back to the main DS
            dsipams.Tables["DTCurrencies"].Clear();
            dsipams.Tables["DTCurrencies"].Merge(currenciesEditor.currenciesTable);
            dsipams.Tables["DTCurrencies"].AcceptChanges();

            dsipams.Tables["DTStocks"].Clear();
            dsipams.Tables["DTStocks"].Merge(stocksEditor.stocks);
            dsipams.Tables["DTStocks"].AcceptChanges();

            dsipams.Tables["DTQuotes"].Clear();
            dsipams.Tables["DTQuotes"].Merge(quotesEditor.quotes);
            dsipams.Tables["DTQuotes"].AcceptChanges();

            dsipams.Tables["DTCurrencyExchangeRate"].Clear();
            dsipams.Tables["DTCurrencyExchangeRate"].Merge(currencyExchangeRatesEditor.currencyExchangeRates);
            dsipams.Tables["DTCurrencyExchangeRate"].AcceptChanges();

            dsipams.Tables["DTQuantityTransactions"].Clear();
            dsipams.Tables["DTQuantityTransactions"].Merge(transactionEditor.transactions);
            dsipams.Tables["DTQuantityTransactions"].AcceptChanges();

            dsipams.Tables["DTDividends"].Clear();
            dsipams.Tables["DTDividends"].Merge(dividendsEditor.dividends);
            dsipams.Tables["DTDividends"].AcceptChanges();

            dsipams.Tables["DTQuantityTransactions"].Clear();
            dsipams.Tables["DTQuantityTransactions"].Merge(transactionEditor.transactions);
            dsipams.Tables["DTQuantityTransactions"].AcceptChanges();
        }

        #endregion

   
        #region Portfolio General Params Setup and Handling

        /// <summary>
        /// Sets the start date.
        /// </summary>
        /// <param name="obj">The obj.</param>
        /// <param name="time">The <see cref="DateTime"/> instance containing the event data.</param>
        private void SetStartDate(object obj, TEventArgs<DateTime> time)
        {
            startDateTime = time.data;
        }

        /// <summary>
        /// Sets the end date.
        /// </summary>
        /// <param name="obj">The obj.</param>
        /// <param name="time">The <see cref="DateTime"/> instance containing the event data.</param>
        private void SetEndDate(object obj, TEventArgs<DateTime> time)
        {
            endDateTime = time.data;
        }

        /// <summary>
        /// Sets the investment house.
        /// </summary>
        /// <param name="obj">The obj.</param>
        /// <param name="house">The <see cref="string"/> instance containing the event data.</param>
        private void SetInvestmentHouse(object obj, TEventArgs<string> house)
        {
            currentInvestmentHouse = house.data;
        }



        #endregion


        /// <summary>
        /// Handles the Click event of the exitToolStripMenuItem control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
      private void exitToolStripMenuItem_Click(object sender, EventArgs e)
      {
        if (MessageBox.Show("Are you sure you want to exit ?\nAll unsaved changes will be lost.", 
            "Confirmation", 
            MessageBoxButtons.OKCancel,
            MessageBoxIcon.Warning, 
            MessageBoxDefaultButton.Button2) == DialogResult.OK)
        {
          Application.Exit();
        }
      }

      /// <summary>
      /// Handles the Click event of the aboutToolStripMenuItem control.
      /// </summary>
      /// <param name="sender">The source of the event.</param>
      /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
      private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
      {
        AboutWindow about = new AboutWindow();
        about.Show();
      }


    }


    
}
